package com.eascs.web.o2o.scheduler.service;

import javax.inject.Inject;

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.stereotype.Service;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.web.o2o.scheduler.dao.CrmDao;
import com.eascs.web.o2o.scheduler.entity.CustomerShopInfoEntity;
import com.eascs.web.o2o.scheduler.util.Constant;

@Service
public class CustomerShopInfoService {

	protected final Logger logger = LoggerFactory.getLogger(getClass());

	@Inject
	BaseDao baseDao;
	@Inject
	CrmDao crmDao;

	/**
	 * @Title: saveAllCustomerShopInfo
	 * @Description: 第一次同步门店信息
	 * @author 张庆奇
	 * @return void
	 * @throws
	 * @date 2016年11月22日 下午2:32:20
	 */
	public void saveAllCustomerShopInfo() {
		
		String sql="SELECT shop.*, basic.id_number AS customer_id_number, 1 AS source_type FROM customer_shop_info shop LEFT JOIN customer_basic_info basic ON shop.customer_id = basic.customer_id WHERE shop.shoptype = 'MAN'";
		//1.同步新系统的门店信息
		logger.info("同步新系统的门店信息开始......");
		try {
			loadCrmData(sql, CustomerShopInfoEntity.class, " delete from customer_shop_info ");
		} catch (Exception e) {
			logger.error("同步新系统的门店信息出现异常......");
			throw e;

		}
		logger.info("同步新系统的门店信息结束......");
		
		
		// 2.同步老系统的门店信息
		logger.info("同步老系统的门店信息开始......");
		sql="SELECT p.loanshopno AS customer_shop_id, p.loanapplyno AS customer_id, 0 AS source_type ,er.idcard AS customer_id_number, p.shoptype, p.shopname, p.setupdate AS stupdate, p.telephone, p.officeaddress AS officeaddress, p.shopaddress AS shopaddress, p.shareholders AS shareholders, p.controllername AS controllername, p.businessarea AS businessarea, p.businesshours AS businesshours, p.shoprent AS shoprent, p.utilitybills AS utilitybills, p.fixedsalary AS fixedsalary, p.mainproducts AS mainproducts, p.shopstock AS shoptock, p.receivable AS receivable, p.payables AS payables, p.eacooperationdate AS eacooperationdate, p.eapurchaseamount AS eapurchaseamount, p.suppliername1 AS suppliername1, p.suppliercooperationdate1 AS suppliercooperationdate1, p.monthamount1 AS monthamount1, p.suppliername2 AS suppliername2, p.suppliercooperationdate2 AS suppliercooperationdate2, p.monthamount2 AS monthamount2, p.suppliername3 AS suppliername3, p.suppliercooperationdate3 AS suppliercooperationdate3, p.monthamount3 AS monthamount3, p.turnover1th, p.turnover2th, p.turnover3th, p.turnover4th, p.turnover5th, p.turnover6th, p.assetsliabilities, p.operatorname, p.shopno, p.isgetdata, p.shopcode, p.islock, p.storeyears AS storyears, p.iscontroller, p.storeinvamount, p.officeprovince, p.officecity, p.officecounty, p.shopprovince, p.shopcity, p.shopcounty, p.vocation FROM eadb.o2o_loanshop p LEFT JOIN eadb.o2o_loaner er ON p.loanapplyno = er.loanapplyno INNER JOIN ( SELECT max(loanshopno) AS loanshopno FROM eadb.o2o_loanshop p LEFT JOIN eadb.o2o_loanapply y ON p.loanapplyno = y.loanapplyno WHERE p.loanapplyno IN ( SELECT max(l.loanapplyno) FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno WHERE y.islock = 'N' AND l.MARRCHILDREN IS NOT NULL GROUP BY l.idcard ) AND p.shoptype = 'MAN' GROUP BY p.loanapplyno ORDER BY p.loanapplyno ) t ON p.loanshopno = t.loanshopno ORDER BY p.loanshopno" ;
		try {
			loadOaShopInfoData(sql, CustomerShopInfoEntity.class, "");
		} catch (Exception e) {
			logger.error("同步新系统的门店信息出现异常......");
			throw e;

		}
		logger.info("同步老系统的门店信息结束......");
		
	}
	
	
	/**
	 * @Title: saveCustomerShopInfo
	 * @Description: 以后增量同步新系统的门店信息
	 * @author 张庆奇
	 * @return void
	 * @throws
	 * @date 2016年11月22日 下午2:32:38
	 */
	@Deprecated
	public void saveCustomerShopInfo() {
		String sql="SELECT shop.*, basic.id_number AS customer_id_number, 1 AS source_type FROM customer_shop_info shop LEFT JOIN customer_basic_info basic ON shop.customer_id = basic.customer_id WHERE shop.shoptype = 'MAN' and date(shop.modify_date) = curdate()  ";
		//1.同步新系统的门店信息
		logger.info("同步新系统的门店信息开始......");
		try {
			loadNewCrmShopInfoData(sql);
		} catch (Exception e) {
			logger.error("同步新系统的门店信息出现异常......");
			throw e;

		}
		logger.info("同步新系统的门店信息结束......");
		
		
		
		
	}
	
	
	
	
	
	/**
	 * @Title: loadCrmData
	 * @Description: 从crm同步数据到数据中心
	 * @author 张庆奇
	 * @param sql
	 * @param requiredType
	 * @param delSql
	 * @return void
	 * @throws @date
	 *             2016年11月22日 下午1:17:23
	 */
	@Deprecated
	public <T> void loadCrmData(String sql, Class<T> requiredType, String delSql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<T> sqlPageQuery = new SqlPageQuery<>(requiredType, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<T> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					baseDao.deleteEntity(delSql);
				}
			}

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());

			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				T obj = pageResult.getData().get(i);
				try {
					baseDao.saveEntity(obj);
				} catch (Exception e) {
					logger.error("数据库异常", e);
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @Title: updateCustomerLoanNum
	 * @Description: 更新数据中心放款次数
	 * @author 张庆奇
	 * @param sql
	 * @param exeSql
	 * @return void
	 * @throws @date
	 *             2016年8月8日 下午3:24:33
	 */
	@Deprecated
	public void updateCustomerLoanNum(String sql, String exeSql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {

			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index,
					sql);
			PageResult<Object[]> pageResult = baseDao.findDataWithPageByMySql(sqlPageQuery);// 查询id,证件号
			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				int loanNum = 0;
				try {
					loanNum = queryLoanCount(objs[2].toString());

					baseDao.excuteSql(exeSql, loanNum, objs[0]);
					crmDao.excuteSql("update customer_basic_info set number_of_loans=?  where customer_id=? ", loanNum,objs[0]);
					logger.info("客户id:{},放款次数为:{}", objs[0].toString(), loanNum);

				} catch (Exception e) {
					logger.info("客户id:{},放款次数为:{}", objs[0].toString(), loanNum);
					logger.error("主键", objs[0]);
					logger.error("更新数据中心放款次数,数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}
	
	/**
	 * @Title: queryLoanCount
	 * @Description: 查询放款次数
	 * @author 张庆奇
	 * @param idcard
	 * @return
	 * @return Integer
	 * @throws @date
	 *             2016年8月16日 下午1:32:45
	 */
	@Deprecated
	public Integer queryLoanCount(String idcard) {
		String sql = "SELECT count(1) FROM loan_base_info where loan_time is not null and idcard=? ";
		
		int count = baseDao.countMysql(sql, idcard);
		return count ;
	}

	/**
	 * @Title: loadNewCrmShopInfoData
	 * @Description: 同步新系统的门店信息
	 * @author 张庆奇
	 * @param sql
	 * @return void
	 * @throws @date
	 *             2016年11月22日 下午2:36:09
	 */
	@Deprecated
	public <T> void loadNewCrmShopInfoData(String sql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<CustomerShopInfoEntity> sqlPageQuery = new SqlPageQuery<>(CustomerShopInfoEntity.class,
					Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<CustomerShopInfoEntity> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				CustomerShopInfoEntity obj = pageResult.getData().get(i);
				try {
					int num = baseDao.countMysql("select count(1) from  customer_shop_info where customer_id_number=? ",
							obj.getCustomerIdNumber());
					if (num == 0) {
						baseDao.saveEntity(obj);
					} else {
						if (obj.getSourceType() == 1) {
							baseDao.updateEntity(obj);// 新系统有，更新数据
						} else {
							// 老系统有数据，删除该数据，新增数据
							baseDao.excuteSql("delete from customer_shop_info where customer_id_number=?",
									obj.getCustomerIdNumber());
							baseDao.saveEntity(obj);
						}
					}
				} catch (DataIntegrityViolationException e) {
					logger.error("数据库异常", e);
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}
	
	/**
	 * @Title: loadOaShopInfoData
	 * @Description: 同步oa门店信息
	 * @author 张庆奇
	 * @param sql
	 * @param requiredType
	 * @param delSql
	 * @return void
	 * @throws @date
	 *             2016年11月22日 下午2:34:06
	 */
	public <T> void loadOaShopInfoData(String sql, Class<T> requiredType, String delSql) {
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<CustomerShopInfoEntity> sqlPageQuery = new SqlPageQuery<>(CustomerShopInfoEntity.class,
					Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<CustomerShopInfoEntity> pageResult = this.baseDao.findDataWithPage(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					baseDao.deleteEntity(delSql);
				}
			}
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				CustomerShopInfoEntity obj = pageResult.getData().get(i);
				try {
					int num = baseDao.countMysql("select count(1) from  customer_shop_info where customer_id_number=? ",
							obj.getCustomerIdNumber());
					if (num == 0) {
						baseDao.saveEntity(obj);
					}
				} catch (Exception e) {
					logger.error("数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	
}
